"""
@author: Zhang Wenzhou
@file: user.py
@time: 2023/3/2 0002 10:34:05
"""
from model import db

class StuInfo(db.Model):
    __tablename__ = "stuinfo"
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.String(128), nullable=False)
    sex = db.Column(db.String(32), nullable=False)
    age = db.Column(db.Integer)

    # def to_json(self):
    #     return {
    #         "id": self.id,
    #         "name": self.name,
    #         "sex": self.sex,
    #         "age": self.age
    #     }

    def keys(self):
        return ( 'id', 'name', 'sex', 'age')

    def __getitem__(self, item):
        return getattr(self, item)

# 查询名i在里面包含sc,并且年龄大于18岁的
# stu1 = StuInfo.query.filter(StuInfo.age>18,StuInfo.name.like("%sc%")).all()

# filter_by 按条件查询，只支持=      ！=、>、<不支持
# filter   条件灵活 大于、小于、等于、不等于、包含、in、and、all
# group_by  分组聚合 --from sqlalchemy import func
# offset    指定偏移量
# limit 限制输出多少条     配合offset做分页显示
# order_by      按照某字段排序 可以升序也可以降序
# .all()    获取所有 -- 五年有没有查询到数据 --返回列表
# .first()  获取一条记录 -- 返回一个数据模型对象

# from model.user import StuInfo
# from model import db
# from sqlalchemy import func


class StuGrade(db.Model):
    __tablename__ = "stu_grade"
    stu_id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    stu_name = db.Column(db.String(128), nullable=False)
    stu_subject = db.Column(db.String(64), nullable=False)
    stu_grade = db.Column(db.Integer, nullable=False)
    stu_class = db.Column(db.String(64), nullable=False)

    def keys(self):
        return ( 'stu_id', 'stu_name', 'stu_subject', 'stu_grade', 'stu_class')

    def __getitem__(self, item):
        return getattr(self, item)

# 1.找出语文80分以上的,学生名字和成绩
# stu1 = db.session.query(StuGrade.stu_name,StuGrade.stu_grade).filter(StuGrade.stu_grade>80,StuGrade.stu_subject=="语文").all()

# 2.找出各科最高分,学生名字和成绩
# stu = db.session.query(StuGrade.stu_name,StuGrade.stu_subject,func.max(StuGrade.stu_grade)).group_by(StuGrade.stu_subject).all()

# 3.按语文成绩降序排序,输出学生名字和成绩
# stu = db.session.query(StuGrade.stu_name,StuGrade.stu_grade).filter(StuGrade.stu_subject=='语文').order_by(desc(StuGrade.stu_grade)).all()

# 4.各班各科的平均分,输出班级 科目 平均分
# stu = db.session.query(StuGrade.stu_class,StuGrade.stu_subject,func.avg(StuGrade.stu_grade)).group_by(StuGrade.stu_class,StuGrade.stu_subject).all()

# 5.各班的总成绩平均，输出班级、总成绩平均分
# stu = db.session.query(StuGrade.stu_class,func.avg(StuGrade.stu_grade)).group_by(StuGrade.stu_class).all()

# 6.找出总分前三名，降序排序。输出名字和总分数
# stu = db.session.query(StuGrade.stu_name,func.sum(StuGrade.stu_grade)).group_by(StuGrade.stu_name).order_by(desc(func.sum(StuGrade.stu_grade))).limit(3).all()


# relationship #
class StuIf(db.Model):
    __tablename__ = "stuif"
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    stu_name = db.Column(db.String(128), nullable=False)
    stu_sex = db.Column(db.String(64), nullable=False)
    stu_age = db.Column(db.Integer, nullable=False)
    # 创建外键约束
    stu_class = db.Column(db.ForeignKey("stuclass.id"))

    def keys(self):
        return ( 'id', 'stu_name', 'stu_sex', 'stu_age', 'stu_class')

    def __getitem__(self, item):
        return getattr(self, item)


class StuClass(db.Model):
    __tablename__ = "stuclass"
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    class_name = db.Column(db.String(128), nullable=False)
    # 创建关联                          反向查询字段
    stu_obj = db.relationship("StuIf", backref="class_obj")
    # 一对一
    # stu_obj = db.relationship("StuIf", backref="class_obj", uselist=False)

